package com.yayan.beautyclub.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;

import com.mysql.jdbc.Statement;
import com.yayan.beautyclub.bean.Reserve;

public class ReserveDao extends ConnectionBase implements IReserveDao {

	public ReserveDao(){}
	
	@Override
	public List<Map<String, Object>> findReserveByUserId(long uId, int status, int page, int size) throws Exception {
		// TODO Auto-generated method stub
		String sqlStr = "select rId,reserve.pId as pId,pName,cName,titlePic,reserve.time as time,reserve.status as status"
				+ " from reserve,products,category where uId=? and reserve.pId=products.pId and products.cId=category.cId";
		if(status != -1){
			sqlStr += " and status=" + status;			
		}
		if(!(page == 0 && size == 0))
			sqlStr += " limit " + (page-1)*size + "," + size;		
		QueryRunner qRunner = new QueryRunner();
		List<Map<String, Object>> list = qRunner.query(getConn(), sqlStr,new MapListHandler(), uId);		
		return list;
	}
	
	@Override
	public List<Map<String, Object>> findReserveByCondition(int page,int size, int firstcId, int secondcId, String searchKey)
			throws Exception {
		// TODO Auto-generated method stub
		String sqlStr = "select rId,pName,titlePic,description,rTime,reserve.time as time,name,phone from reserve,products,category"
				+ " where reserve.pId=products.pId and products.cId=category.cId";
		String sqlNum = "select COUNT(*) as total from reserve,products,category" 
				+ " where reserve.pId=products.pId and products.cId=category.cId";
		if(secondcId > 0){
			sqlStr += " and products.cId=" + secondcId;
			sqlNum += " and products.cId=" + secondcId;
		}else if(firstcId > 0){
			sqlStr += " and (category.parentId=" + firstcId + " or products.cId=" + firstcId + ")";
			sqlNum += " and (category.parentId=" + firstcId + " or products.cId=" + firstcId + ")";
		}
		if(searchKey != null && !searchKey.isEmpty()){			
			String str = "%";
			for(int i = 0; i < searchKey.length(); i++){
				str += searchKey.charAt(i) + "%";
			}			
			sqlStr += " and pName like '" + str + "'";
			sqlNum += " and pName like '" + str + "'";
		}
		
		sqlStr += " and reserve.status=1 and products.status=1 order by reserve.time desc limit "+((page-1)*size) +","+size;
		sqlNum += " and reserve.status=1 and products.status=1";
		
		QueryRunner qRunner = new QueryRunner();
		Map<String,Object> map = qRunner.query(getConn(), sqlNum, new MapHandler());		
		List<Map<String, Object>> list = qRunner.query(getConn(), sqlStr, new MapListHandler());
		list.add(0, map);
		return list;
	}

	@Override
	public Map<String, Object> findReserveById(long rId) throws Exception {
		// TODO Auto-generated method stub
		String sqlStr = "select rTime,pName,price,description,cId,images,cName from reserve,products,category"
				+ " where rId=? and reserve.pId=products.pId and products.cId=category.cId";
		QueryRunner qRunner = new QueryRunner();
		Map<String, Object> map = qRunner.query(getConn(), sqlStr, new MapHandler(), rId);
		return map; 
	}

	@Override
	public long save(Reserve reserve) throws Exception {
		// TODO Auto-generated method stub
		String sqlStr = "insert into reserve(pId,uId,rTime,time,name,phone) values(?,?,?,?,?,?)";
		Connection conn = getConn();
		conn.setAutoCommit(false);
		PreparedStatement ps = conn.prepareStatement(sqlStr, Statement.RETURN_GENERATED_KEYS);
		ps.setLong(1, reserve.getpId());
		ps.setLong(2, reserve.getuId());
		ps.setDate(3, new java.sql.Date(reserve.getrTime().getTime()));
		ps.setString(4, new java.sql.Timestamp(reserve.getTime().getTime()).toString());
		ps.setString(5, reserve.getName());
		ps.setString(6, reserve.getPhone());
		int re = ps.executeUpdate();
		ResultSet rsKey = ps.getGeneratedKeys();
		long r = 0;
		while(re != 0 && rsKey.next()){
			r = rsKey.getLong(1);
		}
		conn.commit();
		conn.setAutoCommit(true);
		close(conn,ps,null);
		return r;
	}

	@Override
	public boolean cancel(long rId) throws Exception {
		// TODO Auto-generated method stub
		String sqlStr = "update reserve set status=0 where rId=?";
		Connection conn = getConn();
		PreparedStatement ps = conn.prepareStatement(sqlStr);
		ps.setLong(1, rId);
		boolean res = ps.executeUpdate() > 0 ? true : false;
		close(conn,ps,null);
		return res;
	}

	@Override
	public boolean delete(long rId) throws Exception {
		// TODO Auto-generated method stub
		String sqlStr = "delete from reserve where rId=?";
		Connection conn = getConn();
		PreparedStatement ps = conn.prepareStatement(sqlStr);
		ps.setLong(1, rId);
		boolean res = ps.executeUpdate() > 0 ? true : false;
		close(conn,ps,null);
		return res;
	}

}
